<?php

namespace app\modules\Product\models;

use Yii;
use app\modules\Order\models\Order;
use app\modules\Order\models\OrderCustomer;
use app\modules\Member\models\Member;
 
class Excel extends \yii\db\ActiveRecord
{
    //订单
    public function Order($model){

        //获取航线所有订单
        $orders = Product::find()->joinWith('allOrder')->where(['product_id'=>$model->id])->asArray()->all();
        
        $customers = [];
        $order_ids = [];
        foreach($orders as $c){
            foreach($c['allOrder'] as $o){
                $order_ids[] = $o['id'];
            }   
        }

        $members = Member::find()->where(['status'=>1, 'type'=>1])->all();
        $members = yii\helpers\ArrayHelper::map($members, 'id', 'name');

        $customers = Order::find()->joinWith("customer")->where(['order.id'=>$order_ids])->all();
        // $customers = OrderCustomer::find()->where(['order_id'=>$order_ids])->all();
      
        $objPHPExcel = new \PHPExcel();

        $sheet = $objPHPExcel->getActiveSheet(0);
      
        $sheet->setTitle('团队名单');
       
        $sheet->getDefaultStyle()->getFont()->setName('宋体');
        $sheet->mergeCells("A1:R1");
        $sheet->getStyle("A1:R1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A1:R1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFC000');
        $sheet->getStyle('A1:R1')->getFont()->setSize(12)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
        $sheet->setCellValue("A1",sprintf("%s %s出发",$model->name,$model->go_time));

        $sheet->getStyle('A2:R2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('0070C0');
        $sheet->getStyle('A2:R2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
        $titleArray = ['序','邮轮舱房','邮轮预定号','酒店','姓名','拼音','性别','出生日期','出生地','护照号','签发地','签发日期','有效期','原件','岸上游','联系电话','销售','备注'];
        $sheet->fromArray($titleArray, NULL, 'A2'); 
        
        $sheet->getColumnDimension('A')->setWidth(3);// 设置宽度
        $sheet->getColumnDimension('M')->setWidth(11);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('P')->setWidth(14);// 设置宽度
        $sheet->getRowDimension(3)->setRowHeight(18);


        foreach($sheet->getRowDimension() as $rd){
             $rd->setRowHeight(-1);
        }

        $key = 0;
        foreach($customers as $order){
            foreach($order->customer as $val){
                $sheet->setCellValue(sprintf("A%d",$key+3),$key+1);
                $sheet->setCellValue(sprintf("E%d",$key+3),$val->first_name.$val->last_name);
                $sheet->setCellValue(sprintf("F%d",$key+3),$val->pinyin);
                $sheet->setCellValue(sprintf("G%d",$key+3),yii::$app->params['gender'][$val->gender]);
                $sheet->setCellValue(sprintf("H%d",$key+3),$val->birthday);
                $sheet->setCellValue(sprintf("I%d",$key+3),'');//出生地 没有
                $sheet->setCellValue(sprintf("J%d",$key+3),$val->passport);
                $sheet->setCellValue(sprintf("K%d",$key+3),$val->passport_add);
                $sheet->setCellValue(sprintf("L%d",$key+3),'');//护照签发日期 没有
                $sheet->setCellValue(sprintf("M%d",$key+3),$val->passport_date);
                $sheet->setCellValue(sprintf("N%d",$key+3),yii::$app->params['passport_type'][$val->passport_type]);
                $sheet->setCellValue(sprintf("O%d",$key+3),'');//岸上游
                $sheet->setCellValue(sprintf("P%d",$key+3),$val->phone);
                $sheet->setCellValue(sprintf("Q%d",$key+3),isset($members[$order->member_id])?$members[$order->member_id]:'');//销售
                $sheet->setCellValue(sprintf("R%d",$key+3),$val->remark);
                $key++;
            }       

        }
        $sheet->getStyle(sprintf("A3:R%d",$key+2))->getFont()->setSize(10);
        $sheet->getStyle(sprintf("A3:R%d",$key+2))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $sheet->getStyle(sprintf("H3:R%d",$key+2))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX32);
        $sheet->getStyle(sprintf("M3:R%d",$key+2))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX32);
        $sheet->getStyle(sprintf("P3:R%d",$key+2))->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
        $sheet->getStyle(sprintf("A1:R%d",$key+2))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

        ///-------sheet1-----------///
        $sheet1 = $objPHPExcel->createSheet(1);
        $sheet1->setTitle('收款信息');
        $sheet1->getDefaultStyle()->getFont()->setName('宋体');

        $sheet1->getRowDimension(1)->setRowHeight(22.5);
        $sheet1->mergeCells("B1:F1");
        $sheet1->getStyle("B1:F1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet1->getStyle('B1:F1')->getFont()->setSize(18)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
        $sheet1->setCellValue("B1","收款信息");
        $sheet1->getStyle('A2:F2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('DCE6F1');
        $sheet1->getStyle('A2:F2')->getFont()->setSize(12)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
        $titleArray = ['订单号','销售','付款单位','收款金额','收款时间','收款银行'];
        $sheet1->fromArray($titleArray, NULL, 'A2'); 

        $dataArray = [
            ['','国旅总社','2016/11/25','22598','2016/12/15','建行'],
            ['','徐兰','2016/11/24','20138','2016/12/12','农行'],
            ['','徐兰','2016/11/24','9469','2016/12/12','农行'],
            ['6789','张廷亮','途牛','5000','2016/12/7','支付宝'],
            ['3210','张廷亮','途牛','10000','2016/12/7','支付宝'],
            ['5678','张廷亮','途牛','6998','2016/12/7','支付宝'],
        ];
            
        $sheet1->getStyle(sprintf("A1:F%d",count($dataArray)+2))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
       
        ///-------sheet2-----------/// 
        $sheet2 = $objPHPExcel->createSheet(2);

        $sheet2->setTitle('优惠信息');


        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/'.date('d').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        return $file;
    }

    //收款
    public function Collect($model)
    {
        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->setTitle('收款信息');
        $sheet->getDefaultStyle()->getFont()->setName('宋体');

        $sheet->getRowDimension(1)->setRowHeight(22.5);
        $sheet->mergeCells("B1:F1");
        $sheet->getStyle("B1:F1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle('B1:F1')->getFont()->setSize(18)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
        $sheet->setCellValue("B1","收款信息");
        $sheet->getStyle('A2:F2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('DCE6F1');
        $sheet->getStyle('A2:F2')->getFont()->setSize(12)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
        $titleArray = ['订单号','销售','付款单位','收款金额','收款时间','收款银行'];
        $sheet->fromArray($titleArray, NULL, 'A2'); 

        $dataArray = [
            ['','国旅总社','2016/11/25','22598','2016/12/15','建行'],
            ['','徐兰','2016/11/24','20138','2016/12/12','农行'],
            ['','徐兰','2016/11/24','9469','2016/12/12','农行'],
            ['6789','张廷亮','途牛','5000','2016/12/7','支付宝'],
            ['3210','张廷亮','途牛','10000','2016/12/7','支付宝'],
            ['5678','张廷亮','途牛','6998','2016/12/7','支付宝'],
        ];
            
        $sheet->getStyle(sprintf("A1:F%d",count($dataArray)+2))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);


        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $file = 'uploads/Collect.xlsx';
        $objWriter->save($file);
        return $file;
    }






}
